2014-05-29

MySQL学习笔记

数据库相关概念

数据库（Database）

保存有组织的数据的容器（通常是一个文件或一组文件）。

表（Table）

某种特定类型数据的结构化清单。

列（Column）

表中的一个字段。所有表都是由一个或多个列组成的。

行（Row）

表中的一个记录。

主键（Primary Key）

一列（或一组列），其值能够唯一区分表中每个行。

最好保证创建的每个表都具有一个主键，以便于管理和维护。

列作为主键，需要满足以下条件：

任意两行都不具有相同的主键值；
每个行都必须具有一个主键值（主键列不允许NULL值）。

几个主键的好习惯：

不更新主键列中的值；
不重用主键列的值；
不再主键列中使用可能会更改的值。

获得数据库和表的信息

查看所有数据库

SHOW DATABASES;

使用数据库

USE dbname;

查看一个数据库内的表的列表

SHOW TABLES;

显示表列

SHOW COLUMNS FROM customers;

或

DESCRIBE customers;

要求提供一个表名。

显示服务器状态信息

SHOW STATUS;

显示授予用户的安全权限

SHOW GRANTS;

显示服务器错误或警告

SHOW ERRORS;
SHOW WARNINGS;

检索数据

检索单个列

SELECT prod_name
FROM products;

上述语句利用 SELECT 从 products 表中检索一个名为 prod_name 的列，注意，这个输出是未排序的。

检索多个列

SELECT prod_id, prod_name, prod_price
FROM products;

检索所有列

SELECT *
FROM products;

注意，检索不需要的列通常会降低应用程序的性能。

检索不同的行

SELECT vend_id
FROM products;

+---------+
| vend_id |
+---------+
|    1001 |
|    1001 |
|    1001 |
|    1002 |
|    1002 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1005 |
|    1005 |
+---------+
14 rows in set (0.00 sec)

当需要检索不同值的列表时，用关键字 DISTINCT 指示MySQL，如下：

SELECT DISTINCT vend_id
FROM products;

+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1005 |
+---------+
4 rows in set (0.00 sec)

注意， DISTINCT 关键字应用于所有列，而不仅是前置它的列。

限制输出结果

为了返回结果中的某些行，可以使用 LIMIT 子句，如下（返回所有结果中的前五行）：

SELECT prod_name
FROM products
LIMIT 5;

+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
| Detonator    |
| Bird seed    |
+--------------+
5 rows in set (0.00 sec)

如果希望返回所有结果中的从行3开始的5行，如下：

SELECT prod_name
FROM products
LIMIT 3, 5;

+--------------+
| prod_name    |
+--------------+
| Detonator    |
| Bird seed    |
| Carrots      |
| Fuses        |
| JetPack 1000 |
+--------------+
5 rows in set (0.00 sec)

注意，检索出来的第一行是行0，而不是行1。

一种替代语法，比如需要从行3开始取4行，可以使用如下语句：

SELECT prod_name
FROM products
LIMIT 4 OFFSET 3;

+-----------+
| prod_name |
+-----------+
| Detonator |
| Bird seed |
| Carrots   |
| Fuses     |
+-----------+
4 rows in set (0.00 sec)

排序检索数据

排序数据

SELECT prod_name
FROM products
ORDER BY prod_name

这条语句指示MySQL对 prod_name 列以字母顺序排序。

按多个列排序

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;

多关键字排序，仅在多个行具有相同的 prod_price 值时才对产品按 prod_name 排序。

指定排序方向

以价格降序排列：

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC;

注意， DESC 关键字只应用到直接位于其前面的列名，如果想在多个列上进行降序排序，需要对每个列指定 DESC 关键字。

使用 ORDER BY 和 LIMIT 的组合，可以找出最大或最小值。

SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

ORDER BY 子句应保证位于 FROM 子句之后，同时也应保证位于 LIMIT 之前。

过滤数据

使用WHERE子句

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50;

+---------------+------------+
| prod_name     | prod_price |
+---------------+------------+
| Carrots       |       2.50 |
| TNT (1 stick) |       2.50 |
+---------------+------------+
2 rows in set (0.00 sec)

这条语句从 products 表中检索两个列，只返回 prod_price 值为2.50的行。

在使用 ORDER BY 和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后。

操作符有 = 、 <> 、 != 、 < 、 <= 、 > 、 >= 、 BETWEEN ，只说下 BETWEEN ，如下：

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| .5 ton anvil   |       5.99 |
| 1 ton anvil    |       9.99 |
| Bird seed      |      10.00 |
| Oil can        |       8.99 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
5 rows in set (0.00 sec)

空值检查

SELECT cust_id, cust_city, cust_email
FROM customers
WHERE cust_email IS NULL;

+---------+-----------+------------+
| cust_id | cust_city | cust_email |
+---------+-----------+------------+
|   10002 | Columbus  | NULL       |
|   10005 | Chicago   | NULL       |
+---------+-----------+------------+
2 rows in set (0.00 sec)

组合WHERE子句

对于 AND 、 OR ，注意 AND 的优先级比 OR 高。

IN 操作符示例：

SELECT vend_id, prod_name, prod_price
FROM products
WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;

+---------+----------------+------------+
| vend_id | prod_name      | prod_price |
+---------+----------------+------------+
|    1003 | Bird seed      |      10.00 |
|    1003 | Carrots        |       2.50 |
|    1003 | Detonator      |      13.00 |
|    1002 | Fuses          |       3.42 |
|    1002 | Oil can        |       8.99 |
|    1003 | Safe           |      50.00 |
|    1003 | Sling          |       4.49 |
|    1003 | TNT (1 stick)  |       2.50 |
|    1003 | TNT (5 sticks) |      10.00 |
+---------+----------------+------------+
9 rows in set (0.00 sec)

NOT 操作符示例：

SELECT vend_id, prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;

+---------+--------------+------------+
| vend_id | prod_name    | prod_price |
+---------+--------------+------------+
|    1001 | .5 ton anvil |       5.99 |
|    1001 | 1 ton anvil  |       9.99 |
|    1001 | 2 ton anvil  |      14.99 |
|    1005 | JetPack 1000 |      35.00 |
|    1005 | JetPack 2000 |      55.00 |
+---------+--------------+------------+
5 rows in set (0.00 sec)

用通配符进行过滤

百分号（ % ）表示任何字符出现任意次数。

例如找出所有以词 jet 起头的产品名字：

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';

+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| JP1000  | JetPack 1000 |
| JP2000  | JetPack 2000 |
+---------+--------------+
2 rows in set (0.00 sec)

下划线（ _ ）表示只匹配单个任意字符。

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

+---------+-------------+
| prod_id | prod_name   |
+---------+-------------+
| ANV02   | 1 ton anvil |
| ANV03   | 2 ton anvil |
+---------+-------------+
2 rows in set (0.00 sec)

用正则表达式进行过滤

基本字符匹配

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.00 sec)

在 LIKE 和 REGEXP 之间有一个重要的差别，就是 LIKE 会匹配整个列，如果被匹配的文本仅在列值中出现， LIKE 不会找到它，而 REGEXP 在列值内进行匹配，如果被匹配的文本在列值中出现， REGEXP 就会找到它。

默认情况下，MySQL中的正则表达式匹配不区分大小写，如果需要区分大小写，可使用 BINARY 关键字，如下：

SELECT prod_name
FROM products
WHERE prod_name REGEXP BINARY 'JetPack .000';

这里的 . 表示匹配任意一个字符。

进行OR匹配

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

匹配几个字符之一

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;

+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)

字符集合也可以否定，只需要在集合开始处放置一个 ^ 即可，比如 [^123] 。

可以用 - 来定义一个范围，比如 [0-9] 匹配数字0到9。

如果需要匹配特殊字符，则需要用为前导，如下：

SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '.'
ORDER BY vend_name;

+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+
1 row in set (0.00 sec)

匹配字符类

预定义的字符类如下：

类	说明
`[:alnum:]`	任意字母和数字（同 `[a-zA-Z0-9]` ）
`[:alpha:]`	任意字符（同 `[a-zA-Z]` ）
`[:blank:]`	空格和制表（同 `[t]` ）
`[:cntrl:]`	ASCII控制字符（ASCII 0到31和127）
`[:digit:]`	任意数字（同 `[0-9]` ）
`[:graph:]`	任意可打印字符，但不包括空格
`[:lower:]`	任意小写字母（同 `[a-z]` ）
`[:print:]`	任意可打印字符
`[:punct:]`	既不在 `[:alnum:]` 又不在 `[:cntrl:]` 中的任意字符
`[:space:]`	包括空格在内的任意空白字符（同 `[fnrtv]` ）
`[:upper:]`	任意大写字母（同 `[A-Z]` ）
`[:xdigit:]`	任意十六进制数字（同 `[a-fA-F0-9]` ）

匹配多个实例

元字符	说明
`*`	0个或多个匹配
`+`	1个或多个匹配
`?`	0个或1个匹配
`{n}`	指定数目的匹配
`{n,}`	不少于指定数目的匹配
`{n,m}`	匹配数目的范围（ `m` 不超过255）

比如下面这个例子：

SELECT prod_name
FROM products
WHERE prod_name REGEXP '([0-9] sticks?)'
ORDER BY prod_name;

+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
2 rows in set (0.00 sec)

s 后面的 ? 使得 s 可选，因为 ? 匹配0个或1个。

下面的例子匹配连在一起的4位数字：

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;

+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)

定位符

元字符	说明
`^`	文本的开始
`$`	文本的结尾
`[[:<:]]`	词的开始
`[[:>:]]`	词的结尾

下面的例子找出一个数（包括以小数点开始的数）开始的所有产品名称：

SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9.]'
ORDER BY prod_name;

+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
3 rows in set (0.00 sec)

创建计算字段

拼接字段

利用多个列的内容组合出一个列返回，使用 Concat() 函数来拼接，如下：

SELECT Concat(vend_name, '(', vend_country, ')')
FROM vendors
ORDER BY vend_name;

+-------------------------------------------+
| Concat(vend_name, '(', vend_country, ')') |
+-------------------------------------------+
| ACME(USA)                                 |
| Anvils R Us(USA)                          |
| Furball Inc.(USA)                         |
| Jet Set(England)                          |
| Jouets Et Ours(France)                    |
| LT Supplies(USA)                          |
+-------------------------------------------+
6 rows in set (0.00 sec)

至于清理多余空格，可以用 Trim() 、 LTrim() 、 RTrim() 来完成。

上面的例子中计算列没有名字，不能用于客户机的应用，可以用 AS 关键字赋予其别名。

SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+
6 rows in set (0.00 sec)

这个 vend_title 就像实际的列一样。

执行算数运算

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+
4 rows in set (0.03 sec)

使用数据处理函数

文本处理函数

函数	说明
`Left()`	返回串左边的字符
`Length()`	返回串的长度
`Locate()`	找出串的一个子串
`Lower()`	将串转换为小写
`LTrim()`	去掉串左边的空格
`Right()`	返回串右边的字符
`RTrim()`	去掉串右边的空格
`Soundex()`	返回串的 `SOUNDEX` 值
`SubString()`	返回子串的字符
`Upper()`	将串转换为大写

其中， SOUNDEX 是一个能对串的发音比较而不是进行字母比较，比如下面的例子：

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie');

+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+
1 row in set (0.05 sec)

其中， WHERE 子句使用 Soundex() 函数来转换 cust_contact 列值和搜索串为它们的 SOUNDEX 值，因为 Y Lie 和 Y Lee 发音相似，所以它们的 SOUNDEX 值匹配。

日期和时间处理函数

常用的日期和时间处理函数

函数	说明
`AddDate()`	增加一个日期（天、周等）
`AddTime()`	增加一个时间（时、分等）
`CurDate()`	返回当前日期
`CurTime()`	返回当前时间
`Date()`	返回日期时间的日期部分
`DateDiff()`	计算两个日期之差
`Date_Add()`	高度灵活的日期运算函数
`Date_Format()`	返回一个格式化的日期或时间串
`Day()`	返回一个日期的天数部分
`DayOfWeek()`	对于一个日期，返回对应的星期几
`Hour()`	返回一个时间的小时部分
`Minute()`	返回一个时间的分钟部分
`Month()`	返回一个日期的月份部分
`Now()`	返回当前日期和时间
`Second()`	返回一个时间的秒部分
`Time()`	返回一个日期时间的时间部分
`Year()`	返回一个日期的年份部分

注意MySQL使用的日期格式，必须为 yyyy-mm-dd ，虽然支持两位数字的年份，但应该总是使用四位数的年份。

基本的日期示例：

SELECT cust_id, order_num
FROM orders
WHERE order_date = '2005-09-01';

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+
1 row in set (0.00 sec)

但这是不可靠的，如果 order_date 值为 2005-09-01 12:00:00 ，那么 WHERE 匹配就会失败，所以应该用上面的函数来进行提取，然后再进行比较：

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';

如果想检索出2005年9月的所有订单，可以这么写：

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

另一种方法是这么写：

SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

数值处理函数

函数	说明
`Abs()`	返回一个数的绝对值
`Cos()`	返回一个角度的余弦
`Exp()`	返回一个数的指数值
`Mod()`	返回除操作的余数
`Pi()`	返回圆周率
`Rand()`	返回一个随机数
`Sin()`	返回一个角度的正弦
`Sqrt()`	返回一个数的平方根
`Tan()`	返回一个角度的正切

汇总数据

聚集函数

函数	说明
`AVG()`	返回某列的平均值
`COUNT()`	返回某列的行数
`MAX()`	返回某列的最大值
`MIN()`	返回某列的最小值
`SUM()`	返回某列值之和

AVG()函数

SELECT AVG(prod_price) AS avg_price
FROM products;

+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.00 sec)

当然可以和 WHERE 组合来确定特定行的平均值。

注意： AVG() 函数忽略列值为 NULL 的行。

COUNT()函数

有两种使用方式，当使用 COUNT(*) 对表中的数目进行计数，不管表列中包含的是 NULL 还是非空值；而是用 COUNT(column) 对特定列中具有的值进行计数，忽略 NULL 值。

比如下面的例子返回 customers 表的总人数：

SELECT COUNT(*) AS num_cust
FROM customers;

+----------+
| num_cust |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

下面的例子只对有电子邮件地址的人计数：

SELECT COUNT(cust_email) AS num_cust
FROM customers;

+----------+
| num_cust |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

而实际上的表是这样的：

mysql> SELECT cust_id, cust_email
    -> FROM customers;
+---------+---------------------+
| cust_id | cust_email          |
+---------+---------------------+
|   10001 | ylee@coyote.com     |
|   10002 | NULL                |
|   10003 | rabbit@wascally.com |
|   10004 | sam@yosemite.com    |
|   10005 | NULL                |
+---------+---------------------+
5 rows in set (0.00 sec)

MAX()、MIN()、SUM()函数

SELECT MIN(prod_price) AS min_price
FROM products;

+-----------+
| min_price |
+-----------+
|      2.50 |
+-----------+
1 row in set (0.01 sec)

它们都会忽略列值为 NULL 的行。

聚集不同值

对于上面的 AVG() 、 SUM() 、 MIN() 、 MAX() 、 COUNT() 聚集函数，都有两个参数可用：

对所有的行执行计算，指定 ALL 参数或者不给参数（默认）；
只包含不同的值，指定 DISTINCT 参数。

比如 AVG() 函数使用 DISTINCT 参数的示例：

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.03 sec)

而不使用 DISTINCT 的时候的平均值是：

SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.00 sec)

说明有多个物品具有相同的较低价格，排除它们后提升了平均价格。

注意， DISTINCT 不能用于 COUNT(*) 。